from tkinter import *
from tkinter import filedialog
from tkinter import ttk
from tkinter import font as tkfont
from threading import Thread
from mini_tools.cjjtool import *
from mini_tools.myexcel import *
from mini_tools.mytools import *
from sql import sqlseverDB
from config import *
import tkinter.messagebox as messagebox
import openpyxl,time,concurrent.futures

titlename='yntitle'

class SecondFra(Frame):
    def __init__(self,master=None):
        Frame.__init__(self,master)
        DCMainFrame(self).pack(fill=X,padx=3,pady=3)

class DCMainFrame(Frame):
    def __init__(self,master=None):
        Frame.__init__(self,master)
        Label(self, text='二次铺货导出_印尼虾皮普货', fg="black", bg="yellow",font=("微软雅黑", 13)).pack(fill=X,padx=5)
        self.ms = sqlseverDB('{SQL Server}', SQL_SERVER, 'SuMaiTongPol', SQL_USER, SQL_PWD)
        self.kwcountStr=StringVar()
        self.kwcountStr.set('正在获取二次普货产品数量...')
        self.createWidgets()

    def createWidgets(self):
        ttk.Separator(self,orient='horizontal').pack(fill=X,pady=2)
        
        f0=Frame(self)
        f0.pack(fill=X)

        lrf=Frame(f0)
        lrf.pack(side=LEFT,fill=Y,padx=2,pady=2)

        f00=Frame(lrf)
        f00.pack(fill=X,padx=2,pady=2)
        Label(f00, textvariable=self.kwcountStr,fg='red').pack(side=LEFT, padx=2, pady=2)
        Thread(target=self.getCount).start()
        label_font = tkfont.Font(family="微软雅黑", size=10, weight="bold")
        f01=Frame(lrf)
        f01.pack(fill=X,padx=2,pady=2)



        f03=Frame(lrf)
        f03.pack(fill=X,padx=2,pady=2)
        Label(f03, text='商家编码:',font=label_font).pack(side=LEFT, padx=2, pady=2)
        self.SqmIpt = Entry(f03,width=10)
        self.SqmIpt.pack(side=LEFT, padx=2, pady=2)
        if get_config('SJBM'):
            self.SqmIpt.insert(0, get_config('SJBM'))

        f04=Frame(lrf)
        f04.pack(fill=X,padx=2,pady=2)
        self.dcbtn=Button(f04,text='二次铺货导出',command=lambda:Thread(target=self.start_dc).start(),fg='#ffffff',bg='green',width=20)
        self.dcbtn.pack(side=LEFT, padx=2, pady=2)


        rf=Frame(f0)
        rf.pack(side=RIGHT,fill=Y)
        rollDiv(rf)

    def getCount(self):
        
        while True:
            try:

                rs1=self.ms.ExecQuery(f'''SELECT COUNT(distinct itemid) from ProShopeePh where xuercipuhuo_yinnixiapi=1''')
                rs2=self.ms.ExecQuery(f'''SELECT COUNT(distinct itemid) from ProShopeePh where xuercipuhuo_yinnixiapi=1 
                                      and yiercipuhuo_yinnixiapi=0''')
                self.kwcountStr.set(f'需二次导出的产品共 {rs1[0][0]} 个,其中 {rs2[0][0]} 个产品未导出')
                
            except Exception as e:
                pass
            time.sleep(10)
    


    def start_dc(self):
        smtys={
            '标题':f'{titlename}Rcode',
            '产品ID':'itemid',
            '详细参数':'attributes',
            'SKU图片网址':'skuImg',
            '价格':'NewPrice_shopeeid',
            'SKUID':'skuid',
            '描述':'pdec'
        }

        zjys={
            'Option for Variation 1':'sku1',
            'Option for Variation 2':'sku2',
        }

        sqm=self.SqmIpt.get().strip()

        if not sqm:
            messagebox.showinfo('提示','请输入商家编码')
            return
        

        update_config({'SJBM':sqm})

        self.dcbtn.config(state=DISABLED) 
        dc_itemids=[]
        print(f'正在获取商家编码:{sqm},需二次导出的数据...')
        sqlstr='''select * from proshopeeph where itemid in
                (select distinct itemid 
                from proshopeeph 
                where xuercipuhuo_yinnixiapi=1 and yiercipuhuo_yinnixiapi=0 and sjbm=?)'''
        rs_pro=self.ms.ExecQuerydict(sqlstr,sqm)
        
        if len(rs_pro)==0:
            print(f'商家编码:{sqm},当前无需二次导出的数据')
            messagebox.showinfo('提示',f'商家编码:{sqm},当前无需二次导出的数据')
            self.dcbtn.config(state=NORMAL) 
            return
        
        print(f'商家编码:{sqm} 需二次导出的数据共{len(rs_pro)}个')
        rp_mbid={}
        for r in rs_pro:
            cur_pmbid=r['pmbid']
            dc_itemids.append(r['id'])
            if rp_mbid.get(cur_pmbid):
                rp_mbid[cur_pmbid].append(r)
            else:
                rp_mbid[cur_pmbid]=[r]

        print(f'商家编码:{sqm},二次导出模版分布:{list(rp_mbid.keys())}')
        dcmr=f'二次铺货导出_{getTimeStr()}'
        os.mkdir(dcmr)
        for mbid,rp in rp_mbid.items():
            new_rp=getsmtpro(rp)
            second_dc_exceldata(new_rp,mbid,dcmr,smtys,zjys)

            print(f'模板id:{mbid},成功二次导出')
            print()
        wstr=','.join(['?' for _ in dc_itemids])
        aff2=ms.ExecNoQuery(f'update proshopeeph set yiercipuhuo_yinnixiapi=1 where id in ({wstr})',dc_itemids)
        print(f'模版id:{mbid},成功标注:{aff2}个sku导出状态')
        print(f'商家:{sqm}二次铺货导出完毕')
        messagebox.showinfo('提示',f'商家:{sqm}二次铺货导出完毕')
        self.dcbtn.config(state=NORMAL)
    

class rollDiv(Frame):

    def __init__(self,master=None):
        Frame.__init__(self, master)
        self.pack(fill=X,pady=2,padx=2)
        self.ms = sqlseverDB('{SQL Server}', SQL_SERVER, 'SuMaiTongPol', SQL_USER, SQL_PWD)
        self.statustxt=StringVar()
        self.statustxt.set('未选择路径')
        self.createWidgets()

    def createWidgets(self):

        f0=Frame(self)
        f0.pack(fill=X,padx=2,pady=2)
        
        f00=Frame(f0)
        f00.pack(pady=5,fill=X,padx=5)
        Label(f00, text='回溯路径:').pack(side=LEFT, padx=2, pady=2)
        self.DaoRuIpt = Entry(f00)
        self.DaoRuIpt.pack(side=LEFT, padx=2, pady=2)
        Button(f00,text='选择回溯路径',fg='#ffffff',bg='green',command=self.choosefile).pack(side=LEFT,padx=2, pady=2)


        f01=Frame(f0)
        f01.pack(fill=X,pady=2,padx=2)
        Label(f01, textvariable=self.statustxt,fg='red').pack(side=LEFT, padx=2, pady=2)

        f02=Frame(f0)
        f02.pack(fill=X,pady=2,padx=2)
        self.bkbtn=Button(f02,text='回溯目录下所有二次铺货产品',fg='#ffffff',bg='green',command=lambda:Thread(target=self.back_data).start())
        self.bkbtn.pack(side=LEFT,padx=2, pady=2)

    
    def choosefile(self):

        select_path=filedialog.askdirectory()
        self.DaoRuIpt.delete(0, END)
        self.DaoRuIpt.insert(0,select_path)
        self.fpath=select_path
        cfs=os.listdir(select_path)
        self.itemids=[]
        for fname in cfs:
            cfpath=f'{select_path}/{fname}'
            wb=openpyxl.load_workbook(cfpath)
            ws=wb['Template']
            id_col=4
            for col in range(1,ws.max_column+1):
                if ws.cell(row=3,column=col).value.lower()=='parent sku':
                    id_col=col
                    break
            
            for row in range(7,ws.max_row+1):
                cur_itemid=ws.cell(row=row,column=id_col).value
                if cur_itemid and (cur_itemid not in self.itemids):
                    self.itemids.append(cur_itemid)
        self.statustxt.set(f'当前目录下共有 {len(self.itemids)} 个产品')
        
    def back_data(self):
        itemids=self.itemids
        if not self.DaoRuIpt.get().strip() or len(itemids)==0:
            messagebox.showinfo('提示','请先选择目录')
            return
    
        askys=messagebox.askyesno('提示',f'确定回溯 {len(itemids)} 个产品的状态值')
        if not askys:
            return
        self.bkbtn.config(state=DISABLED)

        itemidsStr=','.join([f"'{itemid}'" for itemid in itemids])
        sqlStr=f'update ProShopeePh set yiercipuhuo_yinnixiapi=0 and yiercipuhuo_yinnixiapi=1  where itemid in ({itemidsStr})'
        aff=self.ms.ExecNoQuery(sqlStr)

        print('回溯完毕')
        messagebox.showinfo('提示','回溯完毕')

        self.bkbtn.config(state=NORMAL)

# import tkinter
# list_win=tkinter.Tk()
# list_win.wm_geometry("800x650")
# list_win.title('铺货导出')
# SecondFra(list_win).pack(fill=X,padx=3,pady=3)
# list_win.mainloop()